const express=require('express');
const db = require('../utils/database');

const myRouter = express.Router();
//获取
myRouter.get('/production/get',(req,res)=>{
    const page=req.query.page;
    const count=req.query.count;
    const sql=`SELECT * FROM production JOIN out_stock ON p_ck_id=out_stock_id JOIN in_stock ON rk_id=in_stock_id JOIN transport ON trans_id=t_id 
JOIN purchase ON t_shopid=id JOIN goods ON p_item=g_id JOIN class2 ON g_class=c2_id order by p_id LIMIT ${(page-1)*count},${count}`;
    const sql2 = `select count(*) as total from production JOIN out_stock ON p_ck_id=out_stock_id JOIN in_stock ON rk_id=in_stock_id JOIN transport ON trans_id=t_id 
JOIN purchase ON t_shopid=id JOIN goods ON p_item=g_id JOIN class2 ON g_class=c2_id WHERE c2_name LIKE '%${req.query.c2_name}%' AND p_package LIKE '%${req.query.p_package}%';`;
    const sql3 = `SELECT * FROM  production JOIN out_stock ON p_ck_id=out_stock_id JOIN in_stock ON rk_id=in_stock_id JOIN transport ON trans_id=t_id 
JOIN purchase ON t_shopid=id JOIN goods ON p_item=g_id JOIN class2 ON g_class=c2_id
 WHERE c2_name LIKE '%${req.query.c2_name}%' AND p_package LIKE '%${req.query.p_package}%' order by p_id LIMIT ${(page-1)*count},${count}`  ;
   console.log(sql2)
    db.query(sql, (err, data) => {
        if(err){
            console.log(err)
            res.send({error:1,msg:err})
        }else{
            db.query(sql2,(err2,data2)=>{
                if(err2){
                    console.log(err2)
                    res.send({error:1,msg:err2})
                }else{
                   db.query(sql3,(err3,data3)=>{
                if(err3){
                    console.log(err3)
                    res.send({error:1,msg:err3})
                }else{
                    // console.log(data3)
                    res.send({error:0,msg:'查询成功',data:data,total:data2[0].total,searchDate:data3})
                }
            })
                }
            })
        }
    })
})
//增加
myRouter.post('/production/Add', (req, res) => {
 
    const sql = `INSERT INTO production VALUES(NULL,1,'${req.body.p_package}','${req.body.p_date}','${req.body.p_number}',${req.body.p_specifications},'${req.body.house_id}');`;
  console.log(sql)
    db.query(sql,(err,data)=>{
        if(err){
            console.log(err)
            res.send({error:1,msg:err})
        }else{
            res.send({error:0,msg:'添加成功',data:data})
        }
    })
})
// 删除
myRouter.post('/production/del', (req, res) => {
  console.log(req.body.id)
  const sql = 'delete from production where p_id="' + req.body.id + '";';
  
    db.query(sql,(err,data)=>{
        if(err){
            console.log(err)
            res.send({error:1,msg:err})
        }else{
            res.send({error:0,msg:'删除成功'})
        }
    })
})

// 获取仓库对应的产品
//获取
myRouter.get('/production/get1', (req, res) => {
    
const sql=`SELECT DISTINCT c2_name,p_ck_id FROM production JOIN out_stock ON p_ck_id=out_stock_id JOIN in_stock ON rk_id=in_stock_id JOIN transport ON trans_id=t_id 
JOIN purchase ON t_shopid=id JOIN goods ON p_item=g_id JOIN class2 ON g_class=c2_id WHERE out_stock.house_id=${req.query.house_id};`
   db.query(sql,(err,data)=>{
        if(err){
            console.log(err)
            res.send({error:1,msg:err})
        } else {
            
            res.send({error:0,data:data})
        }
    })
})

// 修改
myRouter.get('/production/edit', (req, res) => {

    const sql = `UPDATE production SET p_package='${req.query.p_package}',p_date='${req.query.p_date}',p_number='${req.query.p_number}',p_specifications='${req.query.p_specifications}' WHERE p_id=${req.query.id};`;
  
    db.query(sql,(err,data)=>{
        if(err){
            console.log(err)
            res.send({error:1,msg:err})
        }else{
            res.send({error:0,msg:'修改成功',data:data})
        }
    })
})

module.exports=myRouter;